/*
 * Copyright (c) 2010 CCX(China) Co.,Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * CCX(China) Co.,Ltd. ("Confidential Information").
 * It may not be copied or reproduced in any manner without the express 
 * written permission of CCX(China) Co.,Ltd.
 *
 * Author: zhaosj
 * Date: 2010-3-29 上午09:31:38
 */
package com.xlstodb;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;

import com.util.ConnUtil;

/**
 * @author zhaosj
 *
 */
public class ExcelToDbViaJxl {
	
	public static void main(String[] args) {
		
		//String filePath = "F:\\MYDOCUMENT\\CCX\\指数明细表.xls" ;
		//String tableName = "INDEX_DETAIL" ;
		
		//String filePath = "F:\\MYDOCUMENT\\CCX\\沪深成分明细表.xls" ;
		//String tableName = "INDEX_ELEMENT_DETAIL" ;
		
		String filePath = "F:\\MYDOCUMENT\\CCX\\中诚信企业利差指数明细.xls" ;
		String tableName = "INDEX_DETAIL" ;
		
		Connection newCon = null ;
		Statement newst1 = null ;
		Statement newst2 = null ;
		ResultSet newrs2 = null ;
		
		try {
			
			newCon = ConnUtil.getConnection("targetOracle");
			newst1 = newCon.createStatement();
			newst2 = newCon.createStatement();
			
			InputStream is = new FileInputStream(filePath);
			Workbook rwb = Workbook.getWorkbook(is);

			int sheetTotal = rwb.getNumberOfSheets();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss") ;
			
			for(int sheet = 0 ; sheet < sheetTotal ; sheet++)
			{
				Sheet st = rwb.getSheet(sheet) ;
	
				int rows = st.getRows();
				int cols = st.getColumns();
				
				System.out.println("sheet(" + sheet + ")===" + st.getName());
						
				//流水号字段(递增)
				String colNameSql = "FLOWID1," ;
				Cell cell = null ;
				
				for (int col = 0 ; col < cols ; col++)
				{
					cell = st.getCell(col, 0) ;
					colNameSql += cell.getContents() + "," ;
				}
				if (colNameSql.endsWith(","))
					colNameSql = colNameSql.substring(0, colNameSql.length() - 1);
				
				for (int i = 1; i < rows; ++i) {
					
					String tmpSql = "select max(flowid1) from " + tableName ;
					newrs2 = newst2.executeQuery(tmpSql) ;
					long maxId = 1L ;
					while(newrs2.next())
						maxId = newrs2.getLong(1) ;
					
					String valueSql = String.valueOf(maxId + 1) + "," ;
					
					for (int j = 0; j < cols; ++j) {
						cell = st.getCell(j , i) ;
						String content = cell.getContents() ;
						
						//DATE
						if (cell.getType() == CellType.DATE)
						{
							DateCell dc=(DateCell)cell;
						    content = sdf.format(dc.getDate()) ;
							content = "to_date('" + content + "','yyyy-mm-dd hh24:mi:ss')";
						}
						
						//VARCHAR
						else if (cell.getType() == CellType.LABEL)
						{
							if (content.indexOf("'") >= 0)
								content = content.replaceAll("'", "''");
							content = "'" + content + "'";
						}
						
						valueSql += content + "," ;
					}
					if (valueSql.endsWith(","))
						valueSql = valueSql.substring(0, valueSql.length() - 1) ;
					
					String newSql = "insert into " + tableName + "("+ colNameSql + ") values(" + valueSql + ")" ;
					//System.out.println("sql="+newSql);
					newst1.executeQuery(newSql) ;
				}
			}
			rwb.close();
			newst1.close();
			newst2.close();
			newCon.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		System.out.println("OVER...");
		System.exit(0) ;
	}

}
